package zy.dao.buy.report.impl;

import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.buy.report.BuyReportDAO;
import zy.dto.buy.enter.EnterDetailReportDto;
import zy.dto.buy.enter.EnterDetailSizeReportDto;
import zy.dto.buy.enter.EnterDetailsDto;
import zy.dto.buy.enter.EnterRankDto;
import zy.dto.buy.enter.EnterReportCsbDto;
import zy.dto.buy.enter.EnterReportDto;
import zy.util.StringUtil;

@Repository
public class BuyReportDAOImpl extends BaseDaoImpl implements BuyReportDAO{
	
	private String getEnterReportSQL(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("et_supply_code"))){
			sql.append(" AND et_supply_code = :et_supply_code ");
		}
		if(StringUtil.isNotEmpty(params.get("et_depot_code"))){
			sql.append(" AND et_depot_code = :et_depot_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("et_manager"))){
			sql.append(" AND et_manager = :et_manager ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND et_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND et_ar_date <= :enddate ");
		}
		sql.append(" AND et_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public Integer countEnterReport(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1) FROM");
		sql.append(" (SELECT 1 FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterReportSQL(params));
		if("brand".equals(type)){
			sql.append(" GROUP BY pd_bd_code");
		}else if("type".equals(type)){
			sql.append(" GROUP BY pd_tp_code");
		}else if("supply".equals(type)){
			sql.append(" GROUP BY et_supply_code");
		}else if("product".equals(type)){
			sql.append(" GROUP BY etl_pd_code");
		}
		sql.append(")temp");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	
	@Override
	public Map<String, Object> sumEnterReport(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" SUM(IF(et_type = 0,etl_amount,0)) AS in_amount,");
		sql.append(" SUM(IF(et_type = 1,ABS(etl_amount),0)) AS out_amount,");
		sql.append(" SUM(IF(et_type = 0,etl_amount*etl_unitprice,0)) AS in_money,");
		sql.append(" SUM(IF(et_type = 1,ABS(etl_amount)*etl_unitprice,0)) AS out_money,");
		sql.append(" SUM(etl_amount*etl_retailprice) AS sell_money");
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterReportSQL(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<EnterReportDto> listEnterReport(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" SUM(IF(et_type = 0,etl_amount,0)) AS in_amount,");
		sql.append(" SUM(IF(et_type = 1,ABS(etl_amount),0)) AS out_amount,");
		sql.append(" SUM(IF(et_type = 0,etl_amount*etl_unitprice,0)) AS in_money,");
		sql.append(" SUM(IF(et_type = 1,ABS(etl_amount)*etl_unitprice,0)) AS out_money,");
		sql.append(" SUM(etl_amount*etl_retailprice) AS sell_money,");
		if("brand".equals(type)){
			sql.append(" pd_bd_code AS code,");
			sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS name");
		}else if("type".equals(type)){
			sql.append(" pd_tp_code AS code,");
			sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS name");
		}else if("supply".equals(type)){
			sql.append(" et_supply_code AS code,");
			sql.append(" (SELECT sp_name FROM t_buy_supply sp WHERE sp_code = et_supply_code AND sp.companyid = t.companyid LIMIT 1) AS name");
		}else if("product".equals(type)){
			sql.append(" etl_pd_code AS code,pd_name AS name,pd_no");
		}
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterReportSQL(params));
		if("brand".equals(type)){
			sql.append(" GROUP BY pd_bd_code");
		}else if("type".equals(type)){
			sql.append(" GROUP BY pd_tp_code");
		}else if("supply".equals(type)){
			sql.append(" GROUP BY et_supply_code");
		}else if("product".equals(type)){
			sql.append(" GROUP BY etl_pd_code");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(EnterReportDto.class));
	}

	private String getEnterReportSQL_csb(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("et_supply_code"))){
			sql.append(" AND et_supply_code = :et_supply_code ");
		}
		if(StringUtil.isNotEmpty(params.get("et_depot_code"))){
			sql.append(" AND et_depot_code = :et_depot_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("et_manager"))){
			sql.append(" AND et_manager = :et_manager ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND et_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND et_ar_date <= :enddate ");
		}
		if(StringUtil.isNotEmpty(params.get("cr_code"))){
			sql.append(" AND etl_cr_code = :cr_code ");
		}
		if(StringUtil.isNotEmpty(params.get("sz_code"))){
			sql.append(" AND etl_sz_code = :sz_code ");
		}
		if(StringUtil.isNotEmpty(params.get("br_code"))){
			if("empty".equals(params.get("br_code"))){
				sql.append(" AND etl_br_code = '' ");
			}else {
				sql.append(" AND etl_br_code = :br_code ");
			}
		}
		sql.append(" AND et_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public List<EnterReportCsbDto> listEnterReport_csb(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT etl_id AS id,");
		sql.append(" SUM(etl_amount) AS amount,");
		sql.append(" SUM(etl_amount * etl_unitprice) AS money,");
		if("color".equals(type)){
			sql.append(" etl_cr_code AS code,");
			sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = etl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS name");
		}else if("size".equals(type)){
			sql.append(" etl_sz_code AS code,");
			sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = etl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS name");
		}else if("bra".equals(type)){
			sql.append(" etl_br_code AS code,");
			sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = etl_br_code AND br.companyid = t.companyid LIMIT 1) AS name");
		}
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterReportSQL_csb(params));
		if("color".equals(type)){
			sql.append(" GROUP BY etl_cr_code");
		}else if("size".equals(type)){
			sql.append(" GROUP BY etl_sz_code");
		}else if("bra".equals(type)){
			sql.append(" GROUP BY etl_br_code");
		}
		sql.append(" ORDER BY amount DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(EnterReportCsbDto.class));
	}
	
	
	private String getEnterDetailReportSQL(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("et_supply_code"))){
			sql.append(" AND et_supply_code = :et_supply_code ");
		}
		if(StringUtil.isNotEmpty(params.get("et_depot_code"))){
			sql.append(" AND et_depot_code = :et_depot_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("et_manager"))){
			sql.append(" AND et_manager = :et_manager ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND et_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND et_ar_date <= :enddate ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND etl_pd_code = :pd_code");
		}
		sql.append(" AND et_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public Integer countEnterDetailReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1) FROM");
		sql.append(" (SELECT 1 FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterDetailReportSQL(params));
		sql.append(" GROUP BY etl_pd_code");
		sql.append(")temp");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	
	@Override
	public Map<String, Object> sumEnterDetailReport(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" SUM(etl_amount) AS amount,");
		sql.append(" SUM(etl_amount*etl_unitprice) AS money,");
		sql.append(" SUM(etl_amount*etl_retailprice) AS sell_money");
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterDetailReportSQL(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}
	
	@Override
	public List<EnterDetailReportDto> listEnterDetailReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT pd_code,pd_no,pd_name,");
		sql.append(" SUM(etl_amount) AS amount,");
		sql.append(" SUM(etl_amount*etl_unitprice) AS money,");
		sql.append(" SUM(etl_amount*etl_retailprice) AS sell_money");
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterDetailReportSQL(params));
		sql.append(" GROUP BY etl_pd_code");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(EnterDetailReportDto.class));
	}
	
	@Override
	public List<EnterDetailSizeReportDto> listEnterDetailSizeReport(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT etl_id AS id,etl_cr_code AS cr_code,etl_br_code AS br_code,");
		sql.append(" SUM(etl_amount) AS totalamount,GROUP_CONCAT(CONCAT(etl_sz_code,':',etl_amount)) AS size_amount,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = etl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = etl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getEnterDetailReportSQL(params));
		sql.append(" GROUP BY etl_cr_code,etl_br_code");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(EnterDetailSizeReportDto.class));
	}
	
	private String getEnterDetailsSQL(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("et_supply_code"))){
			sql.append(" AND et_supply_code = :et_supply_code ");
		}
		if(StringUtil.isNotEmpty(params.get("et_depot_code"))){
			sql.append(" AND et_depot_code = :et_depot_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("et_manager"))){
			sql.append(" AND et_manager = :et_manager ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND et_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND et_ar_date <= :enddate ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND etl_pd_code = :pd_code");
		}
		sql.append(" AND et_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public Integer countEnterDetails(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterDetailsSQL(params));
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	
	@Override
	public Map<String, Object> sumEnterDetails(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" SUM(etl_amount) AS etl_amount,");
		sql.append(" SUM(etl_amount*etl_unitprice) AS etl_unitmoney,");
		sql.append(" SUM(etl_amount*etl_retailprice) AS etl_retailmoney");
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterDetailsSQL(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}
	
	@Override
	public List<EnterDetailsDto> listEnterDetails(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT etl_id,et_make_date,etl_number,etl_pd_code,etl_sub_code,etl_sz_code,etl_szg_code,etl_cr_code,etl_br_code,etl_amount,etl_unitprice,etl_retailprice,");
		sql.append(" etl_pi_type,etl_remark,etl_order_number,etl_type,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year,");
		sql.append(" (SELECT sp_name FROM t_buy_supply sp WHERE sp_code = et_supply_code AND sp.companyid = t.companyid LIMIT 1) AS supply_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = et_depot_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = etl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = etl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = etl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterDetailsSQL(params));
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(EnterDetailsDto.class));
	}
	
	private String getEnterRankSQL(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("et_supply_code"))){
			sql.append(" AND et_supply_code = :et_supply_code ");
		}
		if(StringUtil.isNotEmpty(params.get("et_depot_code"))){
			sql.append(" AND et_depot_code = :et_depot_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("et_manager"))){
			sql.append(" AND et_manager = :et_manager ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND et_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND et_ar_date <= :enddate ");
		}
		sql.append(" AND et_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public Integer countEnterRank(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1) FROM");
		sql.append(" (SELECT 1 FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterRankSQL(params));
		if("brand".equals(type)){
			sql.append(" GROUP BY pd_bd_code");
		}else if("type".equals(type)){
			sql.append(" GROUP BY pd_tp_code");
		}else if("supply".equals(type)){
			sql.append(" GROUP BY et_supply_code");
		}else if("product".equals(type)){
			sql.append(" GROUP BY etl_pd_code");
		}else if("season".equals(type)){
			sql.append(" GROUP BY pd_season");
		}
		sql.append(")temp");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	
	@Override
	public Map<String, Object> sumEnterRank(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" SUM(etl_amount) AS amount,");
		sql.append(" SUM(etl_amount*etl_unitprice) AS money,");
		sql.append(" SUM(etl_amount*etl_retailprice) AS sell_money");
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterRankSQL(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}
	
	@Override
	public List<EnterRankDto> listEnterRank(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" SUM(etl_amount) AS amount,");
		sql.append(" SUM(etl_amount*etl_unitprice) AS money,");
		sql.append(" SUM(etl_amount*etl_retailprice) AS sell_money,");
		if("brand".equals(type)){
			sql.append(" pd_bd_code AS code,");
			sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS name");
		}else if("type".equals(type)){
			sql.append(" pd_tp_code AS code,");
			sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS name");
		}else if("supply".equals(type)){
			sql.append(" et_supply_code AS code,");
			sql.append(" (SELECT sp_name FROM t_buy_supply sp WHERE sp_code = et_supply_code AND sp.companyid = t.companyid LIMIT 1) AS name");
		}else if("product".equals(type)){
			sql.append(" etl_pd_code AS code,pd_name AS name,pd_no");
		}else if("season".equals(type)){
			sql.append(" pd_season AS name");
		}
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getEnterRankSQL(params));
		if("brand".equals(type)){
			sql.append(" GROUP BY pd_bd_code");
		}else if("type".equals(type)){
			sql.append(" GROUP BY pd_tp_code");
		}else if("supply".equals(type)){
			sql.append(" GROUP BY et_supply_code");
		}else if("product".equals(type)){
			sql.append(" GROUP BY etl_pd_code");
		}else if("season".equals(type)){
			sql.append(" GROUP BY pd_season");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(EnterRankDto.class));
	}
	
	@Override
	public List<Map<String, Object>> type_level_enter(Map<String,Object> params){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_tp_code AS tp_code,MONTH(et_make_date) AS month,SUM(etl_amount) AS amount,SUM(etl_amount*etl_unitprice) AS money");
		sql.append(" FROM t_buy_enter t");
		sql.append(" JOIN t_buy_enterlist etl ON etl_number = et_number AND etl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = etl_pd_code AND pd.companyid = etl.companyid");
		sql.append(" WHERE 1=1");
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("et_supply_code"))){
			sql.append(" AND et_supply_code = :et_supply_code ");
		}
		sql.append(" AND YEAR(et_make_date) = :year");
		sql.append(" AND et_ar_state = 1");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY pd_tp_code,MONTH(et_make_date)");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params);
	}
	
}
